package cn.itlaobing.dao;

import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;


/**
 * @Description:
 * @Author: WangDong
 * @Date: 2017/11/9 21:27
 */
public class TrainDao extends BaseDao {
    public Map<String,Object> queryByTrainNumAndStation(String trainNum, String startStation, String endStation) throws SQLException {
        QueryRunner qr=new QueryRunner(this.getDataSource());
        String sql="SELECT s1.trainNum,s1.station AS startStation,s2.station AS endStation,abs(s2.sitePrice -s1.sitePrice) AS price FROM station s1,station s2 WHERE s1.trainNum=s2.trainNum AND s1.trainNum=? AND s1.station=? AND s2.station=?";

        MapHandler mapHandler = new MapHandler();
        Map<String,Object> map=qr.query(sql, new ResultSetHandler<Map<String,Object>>() {
            @Override
            public Map<String,Object> handle(ResultSet rs) throws SQLException {
                if(rs.next()){
                    Map<String,Object> map=new HashedMap();
                    map.put("trainNum",rs.getString("trainNum"));
                    map.put("startStation",rs.getString("startStation"));
                    map.put("endStation",rs.getString("endStation"));
                    map.put("price",rs.getFloat("price"));
                    return map;
                }
                return null;
            }
        }, trainNum, startStation, endStation);

        return map;
    }

}
